﻿using Aspose.Cells;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Web.Mvc;
using FriendStar.Application.VBaseData;
using FriendStar.Code;
using FriendStar.Application.SystemManage;
using FriendStar.Application.BaseData;
using System.IO;
using System.Drawing;
using System.Drawing.Imaging;
using System.Web;

namespace FriendStar.Web.Areas.BOM.Controllers
{
    public class CommonController : ControllerBase
    {
        private V_BUS_SJ_DictionaryApp dutyApp = new V_BUS_SJ_DictionaryApp();

        private DutyApp app = new DutyApp();

        private CommonApp common = new CommonApp(System.Web.HttpContext.Current.Server.MapPath(Configs.GetValue("ExcelSQLPath")));

        private string UseUnit = Configs.GetValue("UseUnit");

        [ValidateInput(false)]
        public ActionResult ExcelExport(string sql)
        {
            string savePath = string.Format("~/Content/temp/{0}.xlsx", DateTime.Now.ToString("yyyyMMddHHmmss"));

            sql = sql ?? "select * from V_CPK_A1_BB_StoProStockCata_RB";

            sql = System.Web.HttpUtility.HtmlDecode(sql);

            string realPath = Server.MapPath(savePath);

            DataSet ds = GetDs(sql);

            ExportDataSetToExcelFile(ds, realPath);

            return Redirect(savePath);
        }

        [ValidateInput(false)]
        public ActionResult ExcelExportById(string id, string param)
        {
            string savePath = string.Format("~/Content/temp/{0}.xlsx", DateTime.Now.ToString("yyyyMMddHHmmss"));

            string sql = string.Empty; DataSet ds = null;

            string dir = Server.MapPath("~/Content/temp/");

            string realPath = Server.MapPath(savePath);

            if(!Directory.Exists(dir))
            {
                Directory.CreateDirectory(dir);
            }

            param = System.Web.HttpUtility.HtmlDecode(param);

            sql = string.Format(common.GetSQLByExport(id), UseUnit, param);

            ds = GetDs(sql);

            ExportDataSetToExcelFile(ds, realPath);

            return Redirect(savePath);
        }

        public ActionResult ExportPDF(string sql)
        {
            string savePath = string.Format("~/Content/temp/{0}.pdf", DateTime.Now.ToString("yyyyMMddHHmmss"));

            sql = sql ?? "select * from V_CPK_A1_BB_StoProStockCata_RB";

            string realPath = Server.MapPath(savePath);

            DataSet ds = GetDs(sql);

            ExportDataSetToPDFFile(ds, realPath);

            return Redirect(savePath);
        }

        private DataSet GetDs(string sql)
        {
            DataSet ds = null;

            ds = new DataSet();

            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString()))
            {
                try
                {
                    conn.Open();

                    SqlCommand cmd = new SqlCommand(sql, conn);

                    IDataReader reader = cmd.ExecuteReader();

                    ds.Load(reader, LoadOption.OverwriteChanges, new string[] { "excelExport" });
                }
                finally
                {
                    conn.Close();
                }

            }

            return ds;
        }

        public bool ExportDataSetToExcelFile(DataSet ds, string savePath)
        {
            Workbook workbook = null;

            bool sucess = true;

            List<Type> typeList = null;

            try
            {
                workbook = new Workbook();

                workbook.Worksheets.RemoveAt(0);

                foreach (DataTable dt in ds.Tables)
                {
                    Worksheet cellSheet = workbook.Worksheets.Add(dt.TableName);

                    typeList = new List<Type>();

                    int rowIndex = 0, colIndex = 0, colCount = dt.Columns.Count, rowCount = dt.Rows.Count;
                    //列名的处理
                    for (int i = 0; i < colCount; i++)
                    {
                        typeList.Add(dt.Columns[i].DataType);

                        cellSheet.Cells[rowIndex, colIndex].PutValue(dt.Columns[i].ColumnName);

                        colIndex++;
                    }

                    rowIndex++;

                    for (int i = 0; i < rowCount; i++)
                    {
                        colIndex = 0;

                        for (int j = 0; j < colCount; j++)
                        {
                            try
                            {
                                cellSheet.Cells[rowIndex, colIndex].PutValue(dt.Rows[i][j]);
                            }
                            catch
                            {
                                cellSheet.Cells[rowIndex, colIndex].PutValue(dt.Rows[i][j].ToString());
                            }

                            colIndex++;
                        }
                        rowIndex++;
                    }
                    cellSheet.AutoFitColumns();
                }

                workbook.Save(savePath);

                Console.WriteLine(string.Format("已成功保存excel文件"));
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);

                sucess = false;
            }

            return sucess;
        }

        public bool ExportDataSetToPDFFile(DataSet ds, string savePath)
        {
            Workbook workbook = null;

            bool sucess = true;

            List<Type> typeList = null;

            try
            {
                workbook = new Workbook();

                workbook.Worksheets.RemoveAt(0);

                foreach (DataTable dt in ds.Tables)
                {
                    Worksheet cellSheet = workbook.Worksheets.Add(dt.TableName);

                    cellSheet.PageSetup.LeftMargin = 0.3;

                    cellSheet.PageSetup.RightMargin = 0.3;

                    cellSheet.PageSetup.TopMargin = 1;

                    cellSheet.PageSetup.BottomMargin = 0.5;

                    cellSheet.PageSetup.FooterMargin = 0.5;

                    cellSheet.PageSetup.HeaderMargin = 0.5;

                    cellSheet.PageSetup.Orientation = PageOrientationType.Landscape;

                    cellSheet.PageSetup.CenterHorizontally = true;

                    cellSheet.PageSetup.CenterVertically = true;

                    typeList = new List<Type>();

                    int rowIndex = 0, colIndex = 0, colCount = dt.Columns.Count, rowCount = dt.Rows.Count;
                    //列名的处理
                    for (int i = 0; i < colCount; i++)
                    {
                        typeList.Add(dt.Columns[i].DataType);

                        Style style = cellSheet.Cells[rowIndex, colIndex].GetStyle();

                        style.Font.Name = "微软雅黑";//文字字体

                        style.Font.Size = 10;//文字大小

                        style.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;//应用边界线 左边界线  

                        style.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin; //应用边界线 右边界线  

                        style.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;//应用边界线 上边界线  

                        style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;//应用边界线 下边界

                        cellSheet.Cells[rowIndex, colIndex].SetStyle(style);

                        cellSheet.Cells[rowIndex, colIndex].PutValue(dt.Columns[i].ColumnName);

                        colIndex++;
                    }

                    rowIndex++;

                    for (int i = 0; i < rowCount; i++)
                    {
                        colIndex = 0;

                        for (int j = 0; j < colCount; j++)
                        {
                            try
                            {
                                Style style = cellSheet.Cells[rowIndex, colIndex].GetStyle();
                                style.Font.Name = "微软雅黑";//文字字体
                                style.Font.Size = 10;//文字大小
                                style.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;//应用边界线 左边界线  
                                style.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin; //应用边界线 右边界线  
                                style.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;//应用边界线 上边界线  
                                style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;//应用边界线 下边界

                                cellSheet.Cells[rowIndex, colIndex].SetStyle(style);

                                cellSheet.Cells[rowIndex, colIndex].PutValue(dt.Rows[i][j]);
                            }
                            catch
                            {
                                cellSheet.Cells[rowIndex, colIndex].PutValue(dt.Rows[i][j].ToString());
                            }

                            colIndex++;
                        }
                        rowIndex++;
                    }
                    cellSheet.AutoFitColumns();
                }

                workbook.Save(savePath, SaveFormat.Pdf);

                Console.WriteLine(string.Format("已成功保存PDF文件"));
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);

                sucess = false;
            }

            return sucess;
        }
        //FTypeNum "K_8_LJRKH"
        public ActionResult GetDefaultDate(string FTypeNum)
        {
            var data = dutyApp.GetDefaultDate(Configs.GetValue("UseUnit"), FTypeNum);

            return Content(data.ToJson());
        }

        public ActionResult SetDefaultDate(string start, string end, string type)
        {
            var data = dutyApp.SetDefaultDate(Configs.GetValue("UseUnit"), start, end, OperatorProvider.Provider.GetCurrent().UserName, type);

            return Content(data.ToJson());
        }

        public ActionResult GetQrcode(string text)
        {
            byte[] bt = app.GetQrcode(text);

            return File(bt, "image/jpeg");
        }
        public ActionResult GetBarCode(string text, int width, int height)
        {
            MemoryStream ms = new MemoryStream();

            Bitmap bmp = null;

            bmp = app.GetBarCode(text, width, height);

            bmp.Save(ms, ImageFormat.Jpeg);

            return File(ms.ToArray(), "image/jpeg");
        }
        [AcceptVerbs(HttpVerbs.Post)]
        public ActionResult Uploadify(HttpPostedFileBase Filedata, string noticeID)
        {
            Dictionary<string, object> jsonData = new Dictionary<string, object>();

            bool foo = true;

            string base64String = string.Empty;

            try
            {
                if (null != Filedata)
                {
                    Stream stream = Filedata.InputStream;

                    byte[] bytes = new byte[stream.Length];

                    stream.Read(bytes, 0, bytes.Length);
                    //将byte数组转为base64String
                    base64String = Convert.ToBase64String(bytes);
                }

                jsonData.Add("fileContent", base64String);
            }
            catch (Exception ex)
            {
                jsonData.Add("errMsg", ex.Message);

                foo = false;
            }

            jsonData.Add("success", foo);

            return Content(jsonData.ToJson());
        }



    }
}
